package Dm

import (
	"encoding/json"
	"errors"
	"fmt"
	"db2s/ETL"
	ea "db2s/encryptionAlgorithm"
	"db2s/global"
	"reflect"
	"sort"
	"strconv"
	"strings"
)

type TableColumn struct {
}

// ColumnCommentActive 列备注信息处理
func (or TableColumn) nilStrconvNull(s string) string {
	switch s {
	case "<null>", "<entry>", "null":
		return "NULL"
	default:
		return s
	}
}

// ColumnCommentActive 列备注信息处理
func (or TableColumn) nilStrconvEmpty(s string) string {
	switch s {
	case "<null>", "<entry>":
		return "''"
	default:
		return s
	}
}

// GetColumnType 数据类型判断，是字符串还是数字
func (or TableColumn) GetColumnType(s global.GetColumnTypeInput) (result any, err error) {
	var (
		Type, null []string
	)
	for _, v := range s.ColumnFilter {
		if strings.EqualFold(v, "rowId") {
			return global.GetIndexColumnProperty{
				NullGather: []string{"NO"},
				TypeGather: []string{"rowId"},
			}, nil
		}
		for _, v1 := range s.TableColumn {
			if strings.EqualFold(v1.ColumnName, v) {
				switch strings.ToUpper(func(s1 string) (s string) {
					if n := strings.Index(s1, "("); n != -1 {
						s = s1[:n]
					} else {
						s = s1
					}
					return
				}(v1.DataType)) {
				//case "INT", "NUMBER", "FLOAT", "INTEGER", "SMALLINT", "REAL", "BINARY_DOUBLE", "BINARY_FLOAT", "BINARY_INTEGER", "PLS_INTEGER", "NATURAL", "NATURALN", "POSITIVE", "POSITIVEN", "SIGNTYPE", "SIMPLE_INTEGER", "DECIMAL", "DEC", "NUMERIC":
				//	Type = append(Type, "int")
				case "NUMBER":
					Type = append(Type, "decimal")
				case "FLOAT", "BINARY_FLOAT", "BINARY_DOUBLE":
					Type = append(Type, "float64")
				case "VARCHAR2", "CHAR", "NCHAR", "ROWID", "VARCHAR", "NVARCHAR2":
					Type = append(Type, "string")
				case "SYSDATE", "DATE", "TIMESTAMP", "TIMESTAMP WITH TIME ZONE", "TIMESTAMP WITH LOCAL TIME ZONE":
					Type = append(Type, "datetime")
				default:
					Type = append(Type, "string")
				}
				null = append(null, v1.IsNull)
			}
		}
	}
	return global.GetIndexColumnProperty{
		NullGather: null,
		TypeGather: Type,
	}, nil
}

/*
numberTypeRedefine
在 Oracle 中, NUMBER 类型列的 DATA_LENGTH 属性通常为 22。这里有几点需要解释:
数字精度和标度:
Oracle 中的 NUMBER 类型可以存储带小数点的数值。
NUMBER 类型有两个属性:精度(PRECISION)和标度(SCALE)。
精度指数值可以存储的最大位数,标度指小数点后可以存储的位数。
默认 NUMBER 类型的长度:
当 NUMBER 类型的精度和标度未指定时,Oracle 会默认使用精度 38,标度 0。
这种情况下,DATA_LENGTH 属性会被设置为 22 个字节。
22 个字节的原因:
Oracle 内部使用 16 个字节来存储 38 位精度的数值。
另外 6 个字节用于存储附加信息,如正负号、小数位数等。
所以默认 NUMBER 类型列的 DATA_LENGTH 属性为 22 个字节。
*/
func (or TableColumn) numberTypeRedefine(m global.TableMeta) global.ConvertColumnMeta {
	var (
		ct               global.ConvertColumnMeta
		precision, scale int
		err              error
	)
	//number(p,s)  如何不指定p和s，则默认为number(38,0);即最大38位整数
	//如何只指定p，则s默认为0，即整数。例如number(10)，表示10位整数
	//同时指定p和s，则p必须大于等于s，例如number(10,2)，表示最多10位数字，其中包括两位小数
	//s可以是负数，表示小数点左边的位数。例如number(10,-2)表示10位整数，且最多保留到百位
	ct.GroupType = m.DataType
	ct.Type = "decimal"
	ct.DataLengthUnit = "byte"
	ct.Symbol = true
	ct.FixedSwitch = true
	m.DataPrecision = or.nilStrconvEmpty(m.DataPrecision)
	m.DataScale = or.nilStrconvEmpty(m.DataScale)
	switch {
	case len(m.DataPrecision) == 0 || m.DataPrecision == "''": //number的Precision为*
		switch { //处理小数位精度
		case m.DataScale == "0": //不定长整数位 number、number(*,0)、number(*)
			//Precision: 当 scale 参数为 0 时,NUMBER(*, 0) 中的 * 可以表示 1 到 38 之间的任意精度。
			//Scale: scale 参数被固定为 0，表示只存储整数部分，不存储小数部分。
			//当 scale 为 0 时,Oracle 会使用一种称为 "打包整数" 的存储机制,这种机制可以最大限度地利用存储空间,最多可以存储 40 位有效数字
			//NUMBER(*, 0) 类型最多占用 21 个字节的存储空间
			ct.GroupType = m.DataType
			ct.DataLength = 21
			ct.ShowSize = 40
			ct.Format = "Precision"
		case len(m.DataScale) == 0 || m.DataScale == "''": //不定长整数位 number(*)
			ct.GroupType = m.DataType
			ct.DataLength = 22
			ct.Format = "(max,max)"
		case strings.HasPrefix(strings.TrimSpace(m.DataScale), "-"): //负数开头，表示只有整数，没有小数。例如：number(*,-2)
			if scale, err = or.stringToInt(strings.ReplaceAll(m.DataScale, "-", "")); err != nil {
				return global.ConvertColumnMeta{}
			}
			ct.GroupType = fmt.Sprintf("%v(*,%v)", m.DataType, m.DataScale)
			ct.DataLength = 22
			ct.ShowSize = 40
			ct.Type = "decimal"
			ct.Format = "Precision,(-Scale)"
		default:
			ct.GroupType = fmt.Sprintf("%v(*,%v)", m.DataType, m.DataScale)
			ct.DataLength = 22
			ct.Type = "decimal"
			ct.ShowSize = fmt.Sprintf("%v,%v", 40, m.DataScale)
			ct.Format = "Precision,Scale"
		}
	default: //number(DataPrecision,Scale)
		if precision, err = or.stringToInt(m.DataPrecision); err != nil {
			return global.ConvertColumnMeta{}
		}
		switch m.DataScale {
		case "0":
			ct.GroupType = fmt.Sprintf("%v(%v)", m.DataType, m.DataPrecision)
			ct.DataLength = 22
			ct.ShowSize = precision
			ct.Format = "Precision"
		default:
			ct.GroupType = fmt.Sprintf("%v(%v,%v)", m.DataType, m.DataPrecision, m.DataScale)
			if strings.HasPrefix(strings.TrimSpace(m.DataScale), "-") {
				if scale, err = or.stringToInt(strings.ReplaceAll(m.DataScale, "-", "")); err != nil {
					return global.ConvertColumnMeta{}
				}
				precision = precision + scale
				scale = 0
			} else {
				if scale, err = or.stringToInt(m.DataScale); err != nil {
					return global.ConvertColumnMeta{}
				}
				if scale > 30 {
					scale = 30
				}
			}
			if precision <= scale {
				ct.DataLength = 22
				ct.ShowSize = fmt.Sprintf("%v,%v", precision, precision)
				ct.Format = "Scale,Precision"
			} else if precision > scale {
				ct.DataLength = 22
				ct.ShowSize = fmt.Sprintf("%v,%v", precision, scale)
				ct.Format = "Precision,Scale"
			}
		}
	}
	return ct
}

// FloatTypeRedefine "FLOAT", "BINARY_FLOAT", "BINARY_DOUBLE": //浮点类型
func (or TableColumn) FloatTypeRedefine(v global.TableMeta) global.ConvertColumnMeta {
	var (
		ct global.ConvertColumnMeta
	)
	//float 是1~126的二进制长度，位数需要转换成十进制precision =(precision * 0.30103) 向上取整"DOUBLE PRECISION"
	//binary_float，binary_double 只能存17位有效数字
	ct.DataLengthUnit = "byte"
	ct.FixedSwitch = true
	ct.GroupType = v.DataType
	switch strings.ToUpper(v.DataType) {
	case "FLOAT":
		/*
			Oracle 的 FLOAT 数据类型可以表示约 1 到 38 位十进制有效数字的原因如下:
				IEEE 754 标准:
					Oracle 的 FLOAT 数据类型遵循 IEEE 754 浮点数标准。
					根据 IEEE 754 标准, 64 位浮点数包含 1 位符号位、11 位指数位和 52 位尾数位。
				尾数位于有效数字位数的关系:
					52 位尾数位理论上可以表示约 2^52 ≈ 4.5e+15 个不同的值。
					但由于浮点数的内部表示方式,实际上只能表示约 15-16 位十进制有效数字。
				Oracle FLOAT 的特点:
					Oracle 的 FLOAT 数据类型支持 1 到 126 位之间的二进制精度。
					当声明为 FLOAT(53) 时,等同于 IEEE 754 标准中的 64 位 double 类型。
					38 位十进制有效数字的原因:
					虽然 FLOAT(53) 只能表示 15-16 位十进制有效数字,但是 Oracle 的 FLOAT 数据类型支持更高的二进制精度。
					当声明为更高的二进制精度,例如 FLOAT(126),这就可以表示约 38 位十进制有效数字。
					综上所述,Oracle 的 FLOAT 数据类型可以表示约 1 到 38 位十进制有效数字,是因为 Oracle 支持更高的二进制精度,超出了 IEEE 754 标准中 64 位浮点数的限制。但这需要以更高的存储空间为代价。对于大多数场景,使用 FLOAT(53) 即可满足需求。
		*/
		ct.GroupType = fmt.Sprintf("%v(%v)", v.DataType, v.DataPrecision)
		if precision, err := strconv.Atoi(fmt.Sprintf("%v", v.DataPrecision)); err != nil {
			return global.ConvertColumnMeta{}
		} else {
			ct.ShowSize = int(float64(precision)*0.30103) + 1
		}
		ct.MaxSize = 38
		ct.Type = "float"
		ct.DataLength = 22
		ct.Format = "(precision)"
	case "BINARY_FLOAT":
		/*
			BINARY_FLOAT 使用 IEEE 754 标准的单精度浮点数格式进行存储。这种格式包含以下三个部分:
				符号位 (1 bit)
				指数位 (8 bits)
				尾数位 (23 bits)
			2^23大约能表示7~8位数字
		*/
		ct.GroupType = v.DataType
		ct.Type = "float"
		ct.DataLength = 4
		ct.ShowSize = 23
		ct.MaxSize = 23
		ct.Format = "(precision)"
	case "BINARY_DOUBLE":
		ct.Type = "double"
		ct.DataLength = 8
		ct.ShowSize = 54
		ct.MaxSize = 54
		ct.Format = "(precision)"
	}
	return ct
}
func (or TableColumn) varcharTypeRedefine(v global.TableMeta) global.ConvertColumnMeta {
	var (
		ct                     global.ConvertColumnMeta
		charLength, dataLength int
		err                    error
	)
	if charLength, err = or.stringToInt(v.CharLength); err != nil {
		return global.ConvertColumnMeta{}
	}
	if dataLength, err = or.stringToInt(v.DataLength); err != nil {
		return global.ConvertColumnMeta{}
	}
	ct.Type = "varchar"
	if !strings.HasPrefix(strings.ToUpper(v.DataType), "N") {
		if dataLength > charLength {
			ct.DataLengthUnit = "char"
		} else {
			ct.DataLengthUnit = "byte"
		}
	} else {
		ct.DataLengthUnit = "char"
	}
	ct.ShowSize = charLength
	ct.DataLength = dataLength
	ct.GroupType = fmt.Sprintf("%v(%v)", v.DataType, v.CharLength)
	return ct
}

func (or TableColumn) timestampTypeRedefine(v global.TableMeta) (ct global.ConvertColumnMeta) {
	var (
		err   error
		scale int
	)
	ct.Type = "time"
	ct.FixedSwitch = true
	if scale, err = or.stringToInt(v.DataScale); err != nil {
		return global.ConvertColumnMeta{}
	}
	ct.ShowSize = scale
	if ct.DataLength, err = or.stringToInt(v.DataLength); err != nil {
		return global.ConvertColumnMeta{}
	}
	ct.DataLengthUnit = "byte"
	ct.GroupType = v.DataType
	switch scale {
	case 0:
		ct.Format = "year-month-day hour:minute:second"
		if strings.Contains(v.DataType, "WITH TIME ZONE") {
			ct.Type = "timestamp"
			ct.Format = "year-month-day hour:minute:second.utc"
		}
		return
	case 1:
		ct.Format = "year-month-day hour:minute:second.milli(1)"
		if strings.Contains(v.DataType, "WITH TIME ZONE") {
			ct.Type = "timestamp"
			ct.Format = "year-month-day hour:minute:second.milli(1).utc"
		}
		return
	case 2:
		ct.Format = "year-month-day hour:minute:second.milli(2)"
		if strings.Contains(v.DataType, "WITH TIME ZONE") {
			ct.Type = "timestamp"
			ct.Format = "year-month-day hour:minute:second.milli(2).utc"
		}
		return
	case 3:
		ct.Format = "year-month-day hour:minute:second.milli(3)"
		if strings.Contains(v.DataType, "WITH TIME ZONE") {
			ct.Type = "timestamp"
			ct.Format = "year-month-day hour:minute:second.milli(3).utc"
		}
		return
	case 4:
		ct.Format = "year-month-day hour:minute:second.micro(1)"
		if strings.Contains(v.DataType, "WITH TIME ZONE") {
			ct.Type = "timestamp"
			ct.Format = "year-month-day hour:minute:second.micro(1).utc"
		}
		return
	case 5:
		ct.Format = "year-month-day hour:minute:second.micro(2)"
		if strings.Contains(v.DataType, "WITH TIME ZONE") {
			ct.Type = "timestamp"
			ct.Format = "year-month-day hour:minute:second.micro(2).utc"
		}
		return
	case 6:
		ct.Format = "year-month-day hour:minute:second.micro(3)"
		if strings.Contains(v.DataType, "WITH TIME ZONE") {
			ct.Type = "timestamp"
			ct.Format = "year-month-day hour:minute:second.micro(3).utc"
		}
		return
	case 7:
		ct.Format = "year-month-day hour:minute:second.nano(1)"
		if strings.Contains(v.DataType, "WITH TIME ZONE") {
			ct.Type = "timestamp"
			ct.Format = "year-month-day hour:minute:second.nano(1).utc"
		}
		return
	case 8:
		ct.Format = "year-month-day hour:minute:second.nano(2)"
		if strings.Contains(v.DataType, "WITH TIME ZONE") {
			ct.Type = "timestamp"
			ct.Format = "year-month-day hour:minute:second.nano(2).utc"
		}
		return
	case 9:
		ct.Format = "year-month-day hour:minute:second.nano(3)"
		if strings.Contains(v.DataType, "WITH TIME ZONE") {
			ct.Type = "timestamp"
			ct.Format = "year-month-day hour:minute:second.nano(3).utc"
		}
		return
	}
	return
}
func (or TableColumn) rowIdTypeRedefine(v global.TableMeta) global.ConvertColumnMeta {
	var (
		ct         global.ConvertColumnMeta
		dataLength int
		err        error
	)
	if dataLength, err = or.stringToInt(v.DataLength); err != nil {
		return global.ConvertColumnMeta{}
	}
	/*rowID是一个 18 字节的 CHAR 类型,它由 6 个部分组成:
		数据文件号(1 byte)
		块号(3 bytes)
		行号(2 bytes)
		检查和(2 bytes)
	但是oracle使用base 64编码将18字节的rowID值转换为10个ASCII字符，所以实际存储的时候时，将18字节用base 64编码压缩成10字符，所以all_table_column中data_length显示的是10
	*/
	ct.GroupType = fmt.Sprintf("%v(%v)", v.DataType, dataLength)
	ct.DataLengthUnit = "byte"
	ct.DataLengthUnit = "char"
	ct.Type = "char"
	ct.FixedSwitch = true
	ct.ShowSize = dataLength
	ct.DataLength = 18
	return ct
}
func (or TableColumn) charTypeRedefine(v global.TableMeta) global.ConvertColumnMeta {
	var (
		ct                     global.ConvertColumnMeta
		charLength, dataLength int
		err                    error
	)
	if charLength, err = or.stringToInt(v.CharLength); err != nil {
		return global.ConvertColumnMeta{}
	}
	if dataLength, err = or.stringToInt(v.DataLength); err != nil {
		return global.ConvertColumnMeta{}
	}
	ct.Type = "char"
	ct.FixedSwitch = true
	ct.GroupType = fmt.Sprintf("%v(%v)", v.DataType, v.CharLength)
	if !strings.HasPrefix(v.DataType, "N") {
		if dataLength > charLength { //处理char(10 char)
			ct.DataLengthUnit = "char"
		} else {
			ct.DataLengthUnit = "byte"
		}
	} else {
		ct.DataLengthUnit = "char"
	}
	ct.ShowSize = charLength
	ct.DataLength = dataLength
	return ct
}

func (or TableColumn) textTypeRedefine(v global.TableMeta) global.ConvertColumnMeta {
	var (
		ct global.ConvertColumnMeta
	)
	/*
		常用的文本类型转换。
		long {可变长字符列，最大长度限制是2GB，用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。long是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。}
		blob\clob\nclob {三种大型对象(LOB)，用来保存较大的图形文件或带格式的文本文件，如MiceSoft Word文档，以及音频、视频等非文本文件，最大长度是4GB。LOB有几种类型，取决于你使用的字节的类型，Oracle 8i实实在在地将这些数据存储在数据库内部保存。可以执行读取、存储、写入等特殊操作。}
	*/
	ct.Type = "text"
	ct.DataLengthUnit = "byte"
	ct.GroupType = v.DataType
	switch strings.ToUpper(v.DataType) {
	case "LONG":
		/*
			LONG 数据类型是 Oracle 数据库中用于存储大文本数据的一种数据类型。
			LONG 数据类型的最大长度为 2 GB。这意味着它可以存储最多 2 GB 的文本数据。
			要换算成字符数,需要考虑字符编码。以 UTF-8 编码为例:
			1 个英文字符占 1 个字节
			1 个中文字符占 3 个字节
			因此 2 GB 的 LONG 类型可以存储约 2 GB / 3 = 667 万个中文字符
			需要注意的是, Oracle 不建议再使用 LONG 数据类型,而是建议使用更新的 CLOB 数据类型。
			CLOB 数据类型的最大长度为 4 GB,比 LONG 类型增加了一倍的容量。同时 CLOB 在性能、可操作性等方面也更加优秀。
		*/
		ct.ShowSize = 715827882
		ct.DataLength = 2147483648
	case "CLOB", "NCLOB":
		ct.ShowSize = 1431655765
		ct.DataLength = 4294967296
	}
	return ct
}
func (or TableColumn) lobTypeRedefine(v global.TableMeta) global.ConvertColumnMeta {
	var (
		ct global.ConvertColumnMeta
	)
	ct.Type = "lob"
	ct.DataLengthUnit = "byte"
	ct.ShowSize = 4294967295
	ct.DataLength = 4294967296
	ct.GroupType = v.DataType
	return ct
}
func (or TableColumn) gisTypeRedefine(v global.TableMeta) global.ConvertColumnMeta {
	var (
		ct global.ConvertColumnMeta
	)
	ct.Type = "gis"
	ct.GroupType = v.DataType
	ct.Format = "geometry"
	return ct
}
func (or TableColumn) timeTypeRedefine(v global.TableMeta) global.ConvertColumnMeta {
	var (
		ct         global.ConvertColumnMeta
		dataLength int
		err        error
	)
	if dataLength, err = or.stringToInt(v.DataLength); err != nil {
		return global.ConvertColumnMeta{}
	}
	ct.Type = "time"
	//Oracle内部以二进制的形式存储DATE数据。
	//它包含了:century(1个字节)、year(1个字节)、month(1个字节)、day(1个字节)、hour(1个字节)、minute(1个字节)、second(1个字节),共7个字节。
	ct.FixedSwitch = true
	ct.GroupType = v.DataType
	ct.DataLength = dataLength
	ct.ShowSize = 0
	ct.DataLengthUnit = "byte"
	ct.Format = "year-month-day hour:minute:second"
	return ct
}
func (or TableColumn) stringToInt(s string) (int, error) {
	if l, err := strconv.Atoi(s); err != nil {
		return 0, err
	} else {
		return l, nil
	}
}

// DefaultValueQuotation 处理默认值是否+引号问题，int类型或使用函数则不需要引号
func (or TableColumn) DefaultValueQuotation(m string) bool {
	for _, v := range []string{
		"NUMBER", "FLOAT", "BINARY_FLOAT", "BINARY_DOUBLE",
	} {
		if strings.HasPrefix(strings.ToUpper(m), v) {
			return true
		}
	}
	return false
}
func (or TableColumn) defaultValueFuncName(s string) (p string) {
	switch {
	case strings.HasPrefix(s, "(") && strings.HasSuffix(s, ")"):
		p = s[1 : len(s)-1]
	case !strings.HasPrefix(s, "(") && strings.Contains(s, "(") && strings.HasSuffix(s, ")"):
		if r := strings.Index(s, "("); r != -1 {
			p = s[:r]
		}
	default:
		p = s
	}
	return
}
func getMiddleBracket(s string) (p string) {
	switch {
	case strings.HasPrefix(s, "(") && strings.HasSuffix(s, ")"):
		p = s[1 : len(s)-1]
	case !strings.HasPrefix(s, "(") && strings.Contains(s, "(") && strings.HasSuffix(s, ")"):
		bef := strings.Index(s, "(")
		aft := strings.LastIndex(s, ")")
		if bef != -1 && aft != -1 {
			p = s[bef+1 : aft]
		} else {
			p = s
		}
	default:
		p = s
	}
	return
}
func timeStampToFormat(m global.TableMeta) string {
	switch m.DataScale {
	case "0":
		return "year-month-day hour:minute:second"
	case "1":
		return "year-month-day hour:minute:second.milli(1)"
	case "2":
		return "year-month-day hour:minute:second.milli(2)"
	case "3":
		return "year-month-day hour:minute:second.milli(3)"
	case "4":
		return "year-month-day hour:minute:second.micro(1)"
	case "5":
		return "year-month-day hour:minute:second.micro(2)"
	case "6":
		return "year-month-day hour:minute:second.micro(3)"
	case "7":
		return "year-month-day hour:minute:second.nano(1)"
	case "8":
		return "year-month-day hour:minute:second.nano(2)"
	case "9":
		return "year-month-day hour:minute:second.nano(3)"
	default:
		return "year-month-day hour:minute:second"
	}
}
func timeFormatConvert(s string) (format string) {
	var (
		formatGarth []string
		formatMap   = map[string]string{
			"YYYY": "year",
			"MM":   "month",
			"DD":   "day",
			"HH24": "hour",
			"HH":   "hour",
			"MI":   "minute",
			"SS":   "second",
		}
	)
	q := strings.ToUpper(strings.TrimSpace(s))
	if strings.HasPrefix(q, "'") && strings.HasSuffix(q, "'") {
		q = q[1 : len(q)-1]
	}
	if strings.Contains(q, " ") {
		for _, vv := range strings.Split(q, " ") {
			if strings.Contains(vv, "-") {
				formatGarth = append(formatGarth, strings.Split(vv, "-")...)
			}
			if strings.Contains(vv, ":") {
				formatGarth = append(formatGarth, strings.Split(vv, ":")...)
			}
		}
	}
	for k, v := range formatGarth {
		if r, ok := formatMap[v]; ok {
			switch r {
			case "year", "month", "day":
				if k != 0 {
					format += fmt.Sprintf("-%v", r)
				} else {
					format += fmt.Sprintf("%v", r)
				}
			case "hour", "minute", "second":
				if k != 0 {
					if r == "hour" {
						format += fmt.Sprintf(" %v", r)
					} else {
						format += fmt.Sprintf(":%v", r)
					}
				} else {
					format += fmt.Sprintf("%v", r)
				}
			}
		}
	}
	return
}

func splitLeftKuoHao(s string) (res string) {
	l := strings.Index(s, "(")
	r := strings.Index(s, ")")
	switch {
	case l > 0 && r == -1:
		res = s[l+1:]
	case l == -1 && r > 0:
		res = s[:r]
	case l > 0 && r > 0:
		res = s[l+1 : r]
	default:
		res = s
	}
	return
}
func funcParameterSplit(s string) (r []string) {
	var splitSymbol = ","
	lastCommaIndex := strings.LastIndex(s, splitSymbol)
	// 如果找到逗号，则进行切割
	if lastCommaIndex != -1 {
		r = append(r, splitLeftKuoHao(s[:lastCommaIndex]))   // 前部分
		r = append(r, splitLeftKuoHao(s[lastCommaIndex+1:])) // 后部分
	} else {
		r = append(r, s) // 如果没有逗号，则只添加原字符串
	}
	return
}
func toCharFunc(s string) (format string) {
	parameter := funcParameterSplit(getMiddleBracket(s))
	switch strings.TrimSpace(strings.ToUpper(parameter[0])) {
	case "SYSDATE": //时间转字符串
		format = timeFormatConvert(parameter[1])
	default:
		format = fmt.Sprintf("'%v'", parameter[1])
	}
	return
}
func timeFormatIf(s string) bool {
	for _, v := range []string{
		"YYYY", "MM", "DD", "HH24", "HH", "MI", "SS",
	} {
		if strings.Contains(strings.ToUpper(s), v) {
			return true
		}
	}
	return false
}
func toDateFunc(s string) (format string) {
	parameter := funcParameterSplit(getMiddleBracket(s))
	switch {
	case strings.EqualFold(strings.ToUpper(strings.TrimSpace(parameter[0])), "SYSDATE"): //时间转字符串
		format = timeFormatConvert(parameter[1])
	case strings.HasPrefix(strings.ToUpper(strings.TrimSpace(parameter[0])), "TO_CHAR"):
		parameterSub := funcParameterSplit(getMiddleBracket(parameter[0]))
		format = fmt.Sprintf("%v", parameterSub[1])
		if timeFormatIf(parameterSub[1]) {
			format = fmt.Sprintf("%v", timeFormatConvert(parameter[1]))
		}
	default:
		format = fmt.Sprintf("%v", parameter[1])
		if timeFormatIf(parameter[1]) {
			format = fmt.Sprintf("%v.value", timeFormatConvert(parameter[1]))
		}
	}
	return
}
func toTruncFunc() (format string) {
	return "year-month-day 00:00:00"
}
func (or TableColumn) DefaultValueFunc(m global.TableMeta) string {
	var (
		columnDefault = or.defaultValueFuncName(strings.ToUpper(strings.TrimSpace(m.ColumnDefault)))
	)
	switch {
	case strings.HasPrefix(columnDefault, "SYSTIMESTAMP"), strings.HasPrefix(columnDefault, "CURRENT_TIMESTAMP"):
		return "year-month-day hour:minute:second.micro(3).utc"
	case strings.HasPrefix(columnDefault, "SYSDATE"):
		switch or.defaultValueFuncName(strings.ToUpper(m.DataType)) {
		case "DATE":
			return "year-month-day hour:minute:second"
		case "TIMESTAMP":
			return timeStampToFormat(m)
		default:
			return "year-month-day hour:minute:second.micro(3)"
		}
	case strings.HasPrefix(columnDefault, "TO_CHAR"):
		return toCharFunc(m.ColumnDefault)
	case strings.HasPrefix(columnDefault, "TO_DATE"):
		return toDateFunc(m.ColumnDefault)
	case strings.HasPrefix(columnDefault, "TRUNC"):
		return toTruncFunc()
	case strings.HasPrefix(columnDefault, "SYS_GUID"):
		return "sys_guid"
	case strings.HasPrefix(columnDefault, "USER"):
		return "current_user"
	case strings.HasPrefix(columnDefault, "USERENV('SESSIONID')"):
		return "current_id"
	case strings.HasSuffix(columnDefault, ".NEXTVAL") && strings.HasPrefix(strings.ToLower(m.ColumnType), "number"):
		return "auto_increment"
	default:
		return ""
	}
}
func (or TableColumn) DefaultValue(m global.TableMeta) global.ConvertDefaultValue {
	var (
		ct global.ConvertDefaultValue
	)
	//判断列默认值，如果为空则为null，不为空，则只保留字符串本身，去除始尾的'
	defaultValue := or.nilStrconvNull(strings.TrimSpace(m.ColumnDefault))
	ct.OldValue = defaultValue
	if strings.EqualFold(defaultValue, "") || strings.EqualFold(defaultValue, "''") {
		if or.DefaultValueQuotation(m.DataType) { //int类型
			if strings.EqualFold(m.IsNull, "N") {
				ct.NewValue = ""
			}
		} else {
			if strings.EqualFold(m.IsNull, "N") {
				ct.NewValue = "''"
			}
		}
		ct.NewValue = "NULL"
		return ct
	}
	//去除开头的''
	if strings.HasPrefix(defaultValue, "'") && strings.HasSuffix(defaultValue, "'") {
		defaultValue = defaultValue[1 : len(defaultValue)-1]
	}
	//判断default 是否用到函数
	ct.Format = or.DefaultValueFunc(m)
	ct.NewValue = defaultValue
	return ct
}

// ColumnTypeRedefine 根据相应规范，重新定义数据类型
func (or TableColumn) ColumnTypeRedefine(m global.TableMeta) global.TableMeta {
	var (
		event = "[ColumnTypeRedefine]"
		ccm   global.ConvertColumnMeta
		cdv   global.ConvertDefaultValue
		n     = m
	)
	//字符串类型包含char，varchar2,nchar,nvarchar2,clob,nclob
	//只有char,nchar,varchar2,nvarchar2涉及到长度
	//char和nchar是定长的，如果输入的字符串长度小于size，则会用空格填充
	//nLob和lob的char_length=0
	if m.CharLength == "0" { //数值类型 和lob类型
		switch strings.ToUpper(func(s1 string) string {
			if strings.HasPrefix(strings.ToUpper(s1), "TIMESTAMP") {
				var bef, aft string
				if p := strings.Index(s1, "("); p != -1 {
					bef = s1[:p]
				}
				if p := strings.Index(s1, ")"); p != -1 {
					aft = s1[p+1:]
				}
				if len(bef) > 0 || len(aft) > 0 {
					return strings.TrimSpace(fmt.Sprintf("%v%v", bef, aft))
				}
			}
			return s1
		}(m.DataType)) {
		case "NUMBER": //case "NUMBER", "DECIMAL", "NUMERIC": //定长数值
			ccm = or.numberTypeRedefine(m)
		case "FLOAT", "BINARY_FLOAT", "BINARY_DOUBLE":
			ccm = or.FloatTypeRedefine(m)
		case "ROWID", "UROWID":
			ccm = or.rowIdTypeRedefine(m)
		case "DATE": //时间类型
			ccm = or.timeTypeRedefine(m)
		case "TIMESTAMP", "TIMESTAMP WITH TIME ZONE", "TIMESTAMP WITH LOCAL TIME ZONE":
			ccm = or.timestampTypeRedefine(m)
		case "INTERVAL DAY TO SECOND", "TO_CHAR(SYSDATE,'YYYYMMDD')":
		case "CLOB", "NCLOB", "LONG": //文本类型
			ccm = or.textTypeRedefine(m)
		case "BLOB", "LONG RAW", "RAW": //二进制类型
			ccm = or.lobTypeRedefine(m)
		case "ST_GEOMETRY", "SDO_GEOMETRY":
			ccm = or.gisTypeRedefine(m)
		default:
			WLog.Warn(fmt.Sprintf("(%v) %v The data type %v of column name %v of current table %v.%v does not match, the conversion cannot be performed, and the default value is returned.", 1, event, m.DataType, m.ColumnName, m.DatabaseName, m.TableName))
		}
	} else { //字符串类型
		switch strings.ToUpper(m.DataType) {
		case "CHAR", "NCHAR": //定长字符串类型
			ccm = or.charTypeRedefine(m)
		case "VARCHAR2", "NVARCHAR2":
			ccm = or.varcharTypeRedefine(m)
		default:
			WLog.Warn(fmt.Sprintf("(%v) %v The data type %v of column name %v of current table %v.%v does not match, the conversion cannot be performed, and the default value is returned.", 1, event, m.DataType, m.ColumnName, m.DatabaseName, m.TableName))
		}
	}
	//是否允许为null，yes表示为允许，no表示不允许
	switch m.IsNull {
	case "Y":
		n.IsNull = "YES"
	case "N":
		n.IsNull = "NO"
	}
	cdv = or.DefaultValue(m)
	if strings.EqualFold(cdv.Format, "auto_increment") {
		n.AutoIncrement = true
	}
	n.ColumnDefault = cdv.OldValue
	n.ConvertColumnType = ccm
	n.ColumnType = ccm.GroupType
	n.ConvertDefaultValue = cdv
	return n
}

/*
All Oracle 获取校验表的列信息，包含列名，列序号，列类型
*/
func (or TableColumn) All(s global.GetColumnTypeInput) (result global.AllReturnResult, err error) {
	var (
		event = "[dmAll]"
		f1    any
	)
	result.BaseResult.Sql = fmt.Sprintf("SELECT atc.owner AS \"databaseName\",atc.TABLE_NAME AS \"tableName\",atc.column_name AS \"columnName\",atc.data_type as \"dataType\", atc.DATA_PRECISION as \"dataPrecision\",atc.DATA_SCALE as \"dataScale\",atc.DATA_LENGTH as \"dataLength\",atc.char_length as \"charLength\",atc.COLUMN_id AS \"columnSeq\",atc.NULLABLE AS \"isNull\",'' as \"charset\",'' as \"collationName\",to_char(acc.comments) as \"columnComment\",atc.DATA_DEFAULT as \"columnDefault\" FROM all_tab_columns atc LEFT JOIN all_col_comments acc on atc.OWNER = acc.owner and atc.TABLE_NAME = acc.table_name and atc.COLUMN_NAME = acc.column_name where atc.owner in ('%s') and atc.TABLE_NAME in ('%s') ORDER BY atc.column_id", s.TableInfo.Schema, s.TableInfo.Table)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"], SqlMode: true}
	if len(s.TableInfo.Schema) == 0 || len(s.TableInfo.Table) == 0 || len(s.DB) == 0 || active.DB == nil {
		err = errors.New(fmt.Sprintf("%v sql exec fail. Execution process:{schema:%v,table:%v,sql:%v,shard:%v,db:%v,err:%v}", event, s.TableInfo.Schema, s.TableInfo.Table, result.BaseResult.Sql, "single", s.DB, err))
		return
	}
	if result.BaseResult.TimeOut, f1, err = active.SqlQuery(result.BaseResult.Sql, ETL.SMap); err != nil {
		err = errors.New(fmt.Sprintf("%v sql exec fail. Execution process:{sql:%v,err:%v}", event, result.BaseResult.Sql, err))
		return
	}
	if f1 == nil || len(f1.([]map[string]any)) == 0 {
		err = errors.New(fmt.Sprintf("%v Get the column data of table %v.%v is empty. Execution process{exec sql:%v result:%v}", event, s.TableInfo.Schema, s.TableInfo.Table, result.BaseResult.Sql, f1))
		return
	}
	for _, v := range f1.([]map[string]interface{}) {
		var m = global.TableMeta{}
		var jsonData []byte
		for g, j := range v {
			v[g] = fmt.Sprintf("%v", j)
		}
		jsonData, err = json.Marshal(v)
		if err != nil {
			err = errors.New(fmt.Sprintf("%v []map[string]any strconv json fail. Execution process{strconv data:%v error:%v}", event, v, err))
			return
		}
		err = json.Unmarshal(jsonData, &m)
		if err != nil {
			err = errors.New(fmt.Sprintf("%v []byte json unmarshal struct fail. Execution process{strconv data:%v error:%v}", event, string(jsonData), err))
			return
		}
		n := or.ColumnTypeRedefine(m)
		result.Result = append(result.Result, n)
	}
	return
}

func (or TableColumn) DropColumnSql(s global.GetColumnTypeInput) (any, error) {
	return fmt.Sprintf("alter table `%s`.`%s` drop column `%s`;", s.TableInfo.Schema, s.TableInfo.Table, s.DdlColumn.Name), nil
}

/*
TypeConvert 目标端根据通用协议进行数据类型转换
*/
func (or TableColumn) TypeConvert(s global.TableMeta, factor int) (cType string, cByte int) {
	ct := s.ConvertColumnType
	switch ct.Type {
	case "bit":
		if !strings.EqualFold(ct.Format, "b01") {
			return
		}
		cType = fmt.Sprintf("bit(%v)", ct.ShowSize)
	case "int":
		switch ct.DataLength {
		case 1:
			cType = fmt.Sprintf("TINYINT")
			if ct.Symbol {
				cType = fmt.Sprintf("TINYINT unsigned ")
			}
		case 2:
			cType = fmt.Sprintf("SMALLINT")
			if ct.Symbol {
				cType = fmt.Sprintf("SMALLINT unsigned ")
			}
		case 3:
			cType = fmt.Sprintf("MEDIUMINT")
			if ct.Symbol {
				cType = fmt.Sprintf("MEDIUMINT unsigned ")
			}
		case 4:
			cType = fmt.Sprintf("INT")
			if ct.Symbol {
				cType = fmt.Sprintf("INT unsigned ")
			}
		case 8:
			cType = fmt.Sprintf("BIGINT")
			if ct.Symbol {
				cType = fmt.Sprintf("BIGINT unsigned ")
			}
		}
		cByte = ct.DataLength
	case "decimal":
		switch fmt.Sprintf("%v", reflect.TypeOf(ct.ShowSize)) {
		case "int":
			cType = fmt.Sprintf("decimal(%v,0)", ct.ShowSize)
		case "string":
			cType = fmt.Sprintf("decimal(%v)", ct.ShowSize)
		}
		cByte = ct.DataLength
	case "float":
		var typeName string
		switch fmt.Sprintf("%v", reflect.TypeOf(ct.MaxSize)) {
		case "int":
			typeName = "float"
			if ct.MaxSize.(int) > 23 && ct.MaxSize.(int) <= 54 {
				typeName = "double"
			}
		}
		cType = fmt.Sprintf("%v(%v)", typeName, ct.ShowSize)
		cByte = 4
	case "double":
		cType = fmt.Sprintf("double(%v)", ct.ShowSize)
		cByte = 8
	case "char":
		cType = fmt.Sprintf("char(%v)", ct.ShowSize)
		cByte = ct.ShowSize.(int) * factor
	case "varchar":
		cType = fmt.Sprintf("varchar(%v)", ct.ShowSize)
		cByte = ct.ShowSize.(int) * factor
		if cByte > 255 {
			cByte = cByte + 2
		} else {
			cByte = cByte + 1
		}
	case "time":
		switch fmt.Sprintf("%v", reflect.TypeOf(ct.ShowSize)) {
		case "int":
			switch ct.Format {
			case "year-month-day":
				cType = fmt.Sprintf("date")
				cByte = 3
			case "hour:minute:second":
				cType = fmt.Sprintf("time(0)")
				cByte = 3
			case "hour:minute:second.milli(1)", "hour:minute:second.milli(2)", "hour:minute:second.milli(3)":
				cType = fmt.Sprintf("time(%v)", ct.ShowSize)
				cByte = 3
			case "hour:minute:second.micro(1)", "hour:minute:second.micro(2)", "hour:minute:second.micro(3)":
				cType = fmt.Sprintf("time(%v)", ct.ShowSize)
				cByte = 3
			case "year":
				cType = fmt.Sprintf("year")
				cByte = 1
			case "year-month-day hour:minute:second":
				cType = fmt.Sprintf("datetime(%v)", ct.ShowSize)
				cByte = 8
			case "year-month-day hour:minute:second.milli(1)", "year-month-day hour:minute:second.milli(2)", "year-month-day hour:minute:second.milli(3)":
				cType = fmt.Sprintf("datetime(%v)", ct.ShowSize)
				cByte = 8
			case "year-month-day hour:minute:second.micro(1)", "year-month-day hour:minute:second.micro(2)", "year-month-day hour:minute:second.micro(3)":
				cType = fmt.Sprintf("datetime(%v)", ct.ShowSize)
				cByte = 8
			}
		}
	case "timestamp":
		switch fmt.Sprintf("%v", reflect.TypeOf(ct.ShowSize)) {
		case "int":
			switch ct.Format {
			case "year-month-day hour:minute:second.utc":
				cType = fmt.Sprintf("timestamp(%v)", ct.ShowSize)
			case "year-month-day hour:minute:second.milli(1)", "year-month-day hour:minute:second.milli(2)", "year-month-day hour:minute:second.milli(3)":
				cType = fmt.Sprintf("datetime(%v)", ct.ShowSize)
			case "year-month-day hour:minute:second.milli(1).utc", "year-month-day hour:minute:second.milli(2).utc", "year-month-day hour:minute:second.milli(3).utc":
				cType = fmt.Sprintf("timestamp(%v)", ct.ShowSize)
			case "year-month-day hour:minute:second.micro(1).utc", "year-month-day hour:minute:second.micro(2).utc", "year-month-day hour:minute:second.micro(3).utc":
				cType = fmt.Sprintf("timestamp(%v)", ct.ShowSize)
			case "year-month-day hour:minute:second.micro(1)", "year-month-day hour:minute:second.micro(2)", "year-month-day hour:minute:second.micro(3)":
				cType = fmt.Sprintf("datetime(%v)", ct.ShowSize)
			case "year-month-day hour:minute:second.nano(1)", "year-month-day hour:minute:second.nano(2)", "year-month-day hour:minute:second.nano(3)":
				cType = fmt.Sprintf("datetime(6)")
			case "year-month-day hour:minute:second.nano(1).utc", "year-month-day hour:minute:second.nano(2).utc", "year-month-day hour:minute:second.nano(3).utc":
				cType = fmt.Sprintf("timestamp(6)")
			}
		}
		cByte = 4
	case "text":
		switch fmt.Sprintf("%v", reflect.TypeOf(ct.ShowSize)) {
		case "int":
			switch {
			case ct.ShowSize.(int) <= 255:
				cType = fmt.Sprintf("TINYTEXT")
			case ct.ShowSize.(int) <= 65535:
				cType = fmt.Sprintf("TEXT")
			case ct.ShowSize.(int) <= 16777215:
				cType = fmt.Sprintf("MEDIUMTEXT")
			case ct.ShowSize.(int) <= 4294967295:
				cType = fmt.Sprintf("LONGTEXT")
			default:
				cType = fmt.Sprintf("LONGTEXT")
			}
		}
	case "lob":
		switch fmt.Sprintf("%v", reflect.TypeOf(ct.ShowSize)) {
		case "int":
			switch {
			case ct.ShowSize.(int) <= 255:
				cType = fmt.Sprintf("BINARY(%v)", ct.ShowSize)
			case ct.ShowSize.(int) > 255:
				cType = fmt.Sprintf("VARBINARY(%v)", ct.ShowSize)
			}
		}
	}
	return
}
func (or TableColumn) AddColumnSql(s global.GetColumnTypeInput) (any, error) {
	var (
		TypeConstraint string
		nullConstraint = fmt.Sprintf(" NULL ")
		ChartSetValue  string
		CollationValue string
		DefaultValue   string
		CommentValue   string
		factor         = 1
	)
	TypeConstraint, _ = or.TypeConvert(s.DdlColumn.SMeta, factor)
	if strings.EqualFold(s.DdlColumn.SMeta.IsNull, "NO") {
		nullConstraint = fmt.Sprintf(" NOT NULL ")
	}
	if len(s.DdlColumn.SMeta.ColumnDefault) > 0 || strings.EqualFold(s.DdlColumn.SMeta.ColumnDefault, "''") {
		DefaultValue = fmt.Sprintf("DEFAULT %v", s.DdlColumn.SMeta.ColumnDefault)
	}
	if len(s.DdlColumn.SMeta.ColumnComment) > 0 {
		CommentValue = fmt.Sprintf("comment %v", s.DdlColumn.SMeta.ColumnComment)
	} else {
		CommentValue = fmt.Sprintf("comment ''")
	}
	return fmt.Sprintf("alter table `%s`.`%s` add column `%s` %v %v %v %v %v %v;", s.TableInfo.Schema, s.TableInfo.Table, s.DdlColumn.Name, TypeConstraint, nullConstraint, ChartSetValue, CollationValue, DefaultValue, CommentValue), nil
}
func (or TableColumn) DefaultValueConvert(dataType string, l global.ConvertDefaultValue) string {
	switch {
	case len(l.Format) == 0:
		switch l.NewValue {
		case "<entry>", "''":
			return "''"
		case "<null>", "NULL":
			return "NULL"
		default:
			if !or.DefaultValueQuotation(dataType) {
				return fmt.Sprintf("'%v'", l.NewValue)
			}
			return l.NewValue
		}
	case len(l.Format) > 0:
		switch l.Format {
		case "year-month-day hour:minute:second.micro(3).utc", "year-month-day hour:minute:second.micro(3)":
			return "CURRENT_TIMESTAMP"
		case "year-month-day hour:minute:second":
			return "CURRENT_TIMESTAMP"
		case "hour:minute:second":
			return "CURRENT_TIME"
		case "current_user@host":
			return "USER()"
		case "current_sys_user":
			return "SYSTEM_USER()"
		case "randValue":
			return "RAND()"
		case "uuid":
			return "UUID()"
		case "sys_guid":
			return "SYS_GUID"
		case "current_user":
			return "CURRENT_USER()"
		case "current_id":
			return "CONNECTION_ID()"
		}
	}
	return ""
}
func (or TableColumn) ModifyColumnSql(s global.GetColumnTypeInput) (any, error) {
	var (
		TypeConstraint string
		nullConstraint = fmt.Sprintf(" NULL ")
		ChartSetValue  string
		CollationValue string
		DefaultValue   string
		CommentValue   string
		factor         = 1
		record         string
		alterSql       string
	)
	//处理因异构数据库导致的差异
	/*
		1：因为主键索引导致的null值和not null的差异
		2：因为null和not null的差异导致的default值的差异
	*/
	var abGather = make(map[string]int)
	if l, err := global.StructSubJsonNameToMap(*s.DdlColumn.Attributes); err != nil {
		return nil, err
	} else {
		for k, v := range l {
			if v.(bool) {
				abGather[k]++
			}
		}
	}
	if s.DdlColumn.Attributes.Type {
		TypeConstraint, _ = or.TypeConvert(s.DdlColumn.SMeta, factor)
	} else {
		TypeConstraint = s.DdlColumn.DMeta.ColumnType
	}
	partTrue := strings.Contains(strings.ToLower(strings.Join(s.DdlColumn.PartColumn, ",")), strings.ToLower(s.DdlColumn.Name))
	priTrue := strings.Contains(strings.ToLower(strings.Join(s.DdlColumn.PriColumn, ",")), strings.ToLower(s.DdlColumn.Name))
	if strings.EqualFold(s.DdlColumn.SMeta.IsNull, "YES") && s.DdlColumn.Attributes.Null {
		if partTrue && priTrue {
			record = fmt.Sprintf("primary key include partition column must not null")
		} else if priTrue && !partTrue {
			record = fmt.Sprintf("primary key must not null")
		}
		nullConstraint = fmt.Sprintf(" NOT NULL ")
	} else if strings.EqualFold(s.DdlColumn.SMeta.IsNull, "NO") && s.DdlColumn.Attributes.Null {
		nullConstraint = fmt.Sprintf(" NOT NULL ")
	}
	if len(s.DdlColumn.SMeta.ColumnDefault) > 0 || strings.EqualFold(s.DdlColumn.SMeta.ColumnDefault, "''") {
		DefaultValue = fmt.Sprintf(" DEFAULT %v", or.DefaultValueConvert(s.DdlColumn.SMeta.DataType, s.DdlColumn.SMeta.ConvertDefaultValue))
	} else {
		if strings.EqualFold(strings.TrimSpace(nullConstraint), "NOT NULL") {
			DefaultValue = fmt.Sprintf(" DEFAULT '' ")
		}
	}
	if len(s.DdlColumn.SMeta.ColumnComment) > 0 {
		comment := s.DdlColumn.SMeta.ColumnComment
		if strings.Contains(comment, "'") {
			comment = strings.ReplaceAll(comment, "'", "\\'")
		}
		if strings.Contains(comment, ";") {
			comment = strings.ReplaceAll(comment, ";", "\\;")
		}
		CommentValue = fmt.Sprintf("comment '%v'", comment)
	} else {
		CommentValue = fmt.Sprintf("comment ''")
	}
	alterSql = fmt.Sprintf("alter table `%s`.`%s` modify column `%s` %v %v %v %v %v %v;", s.TableInfo.Schema, s.TableInfo.Table, s.DdlColumn.Name, TypeConstraint, nullConstraint, ChartSetValue, CollationValue, DefaultValue, CommentValue)
	if _, ok := abGather["null"]; ok && len(abGather) == 1 {
		alterSql = ""
	}
	return global.AlterSqlResult{
		Record: record,
		Sql:    alterSql,
	}, nil
}
func (or TableColumn) subPartSumMax(s []global.TablePartitionConfig) int {
	var sumMax int
	for _, v := range s {
		p := len(v.SubPartMeta)
		if p > sumMax {
			sumMax = p
		}
	}
	return sumMax
}

func (or TableColumn) createTableSqlPartitionBody(s global.GetColumnTypeInput) (record []global.HeterogeneousConvertRecord, partitionColumn []string, partitionTableSql string) {
	var (
		partBodyColumn                            = make(map[string][]string)
		partColumnType                            = make(map[string]string)
		pm                                        []global.TablePartitionConfig
		partBodyColumnMap                         = make(map[string][]string)
		partBodyColumnHead, subpartBodyColumnHead string
		partitionRules                            []string
		subPartSumMax                             int
	)
	if _, ok := s.TableMeta.PartitionMeta["source"]; !ok || len(s.TableMeta.PartitionMeta) == 0 {
		return
	}
	pm = s.TableMeta.PartitionMeta["source"]
	if len(pm) == 0 {
		return
	}
	if len(pm[0].FirstPartition.ColumnName) > 0 {
		partBodyColumnMap["first"] = pm[0].FirstPartition.ColumnName
	}
	if len(pm[0].SubPartMeta) > 0 {
		partBodyColumnMap["sub"] = pm[0].SubPartMeta[0].ColumnName
	}
	//计算hash sub 分区最大值
	subPartSumMax = or.subPartSumMax(s.TableMeta.PartitionMeta["source"])
	//处理主、子分区列及是否用到函数
	for _, w := range s.TableMeta.ColumnMeta["source"] {
		for k, v := range partBodyColumnMap {
			for _, p := range v {
				if strings.EqualFold(w.ColumnName, p) {
					partitionColumn = append(partitionColumn, w.ColumnName)
					partColumnType[p] = w.ConvertColumnType.Type
					switch {
					case strings.EqualFold(w.ConvertColumnType.Type, "decimal"):
						partBodyColumn[k] = append(partBodyColumn[k], fmt.Sprintf("floor(`%v`)", p))
						record = append(record, global.HeterogeneousConvertRecord{
							Object:   "partition column",
							Logo:     "func convert",
							OldValue: p,
							NewValue: fmt.Sprintf("floor(`%v`)", p),
							Reason:   "partition column data type decimal must is int",
						})
					case len(pm[0].FirstPartition.Func) > 0: //列使用到函数
						partBodyColumn[k] = append(partBodyColumn[k], fmt.Sprintf("%v(`%v`)", pm[0].FirstPartition.Func, p))
						record = append(record, global.HeterogeneousConvertRecord{
							Object:   "partition column",
							Logo:     "func convert",
							OldValue: fmt.Sprintf("%v(%v)", pm[0].FirstPartition.Func, p),
							NewValue: fmt.Sprintf("%v(`%v`)", pm[0].FirstPartition.Func, p),
							Reason:   "partition column data type func must Convert",
						})
					default:
						partBodyColumn[k] = append(partBodyColumn[k], fmt.Sprintf("`%v`", p))
					}
				}
			}
		}
	}
	//处理主分区列
	if columnX, ok := partBodyColumn["first"]; ok {
		if len(columnX) > 1 {
			partBodyColumnHead = fmt.Sprintf("columns (%v)", strings.Join(columnX, ","))
			record = append(record, global.HeterogeneousConvertRecord{
				Object:   "partition column",
				Logo:     "multiple column",
				OldValue: strings.Join(columnX, ","),
				NewValue: fmt.Sprintf("columns (%v)", strings.Join(columnX, ",")),
				Reason:   "partition multiple columns conversion",
			})
		} else if len(columnX) == 1 {
			for k, v := range partColumnType {
				if strings.Contains(columnX[0], k) {
					switch v {
					case "char", "varchar", "time", "timestamp":
						partBodyColumnHead = fmt.Sprintf("columns (%v)", columnX[0])
						record = append(record, global.HeterogeneousConvertRecord{
							Object:   "partition column",
							Logo:     "partition column type",
							OldValue: columnX[0],
							NewValue: fmt.Sprintf("columns (%v)", columnX[0]),
							Reason:   "partition column data type non-int conversion",
						})
					default:
						partBodyColumnHead = fmt.Sprintf("(%v)", columnX[0])
					}
				}
			}
		}
	}
	if columnX, ok := partBodyColumn["sub"]; ok {
		if len(columnX) > 1 {
			subpartBodyColumnHead = fmt.Sprintf("columns (%v)", strings.Join(columnX, ","))
		} else if len(columnX) == 1 {
			for k, v := range partColumnType {
				if strings.Contains(columnX[0], k) {
					switch v {
					//case "char", "varchar", "time", "timestamp":
					//	subpartBodyColumnHead = fmt.Sprintf("columns (%v)", columnX[0])
					default:
						subpartBodyColumnHead = fmt.Sprintf("(%v)", columnX[0])
					}
				}
			}
		}
		subpartBodyColumnHead = fmt.Sprintf("SUBPARTITION BY %v%v SUBPARTITIONS %v", s.TableMeta.PartitionMeta["source"][0].SubPartMeta[0].Meth, subpartBodyColumnHead, subPartSumMax)
	}
forLoop:
	for _, v := range pm {
		switch strings.ToUpper(v.FirstPartition.Meth) {
		case "RANGE":
			var subRulesGather []string
			pr := fmt.Sprintf("PARTITION %v VALUES LESS THAN (%v)", v.FirstPartition.Name, v.FirstPartition.Rules)
			switch v.SubPartMeth {
			case "HASH":
				for _, w := range v.SubPartMeta {
					subRulesGather = append(subRulesGather, fmt.Sprintf("SUBPARTITION `%v`", w.Name))
				}
				if len(v.SubPartMeta) < subPartSumMax {
					for k := 0; k < subPartSumMax-len(v.SubPartMeta); k++ {
						subRulesGather = append(subRulesGather, fmt.Sprintf("SUBPARTITION `add_sub_%v`", k))
						record = append(record, global.HeterogeneousConvertRecord{
							Object:   "sub partition",
							Logo:     "miss partition",
							OldValue: "",
							NewValue: fmt.Sprintf("SUBPARTITION `add_sub_%v`", k),
							Reason:   "sub partition sum is missing and is less than the defined quantity",
						})
					}
				}
			}
			if len(subRulesGather) > 0 {
				pr = fmt.Sprintf("%v(\n\t%v\n\t)", pr, strings.Join(subRulesGather, ",\n\t"))
			}
			partitionRules = append(partitionRules, pr)
		case "LIST":
			partitionRules = append(partitionRules, fmt.Sprintf("PARTITION %v VALUES IN (%v)", v.FirstPartition.Name, v.FirstPartition.Rules))
		case "HASH":
			partitionRules = append(partitionRules, fmt.Sprintf("PARTITIONS %v ", len(pm)))
			break forLoop
		case "KEY":
			partitionRules = append(partitionRules, fmt.Sprintf("PARTITION %v ", len(pm)))
			break forLoop
		}
	}
	if len(partitionRules) > 0 {
		partitionTableSql = fmt.Sprintf("PARTITION BY %v %v %v (\n %v \n)", pm[0].FirstPartition.Meth, partBodyColumnHead, subpartBodyColumnHead, strings.Join(partitionRules, ",\n"))
	}
	return
}

func (or TableColumn) CreateIndex(s global.GetColumnTypeInput, partitionColumn []string, factor int) (recover []global.HeterogeneousConvertRecord, notNull []string, indexSql string) {
	var (
		creatText          []string
		indexMaxLength     = make(map[string]int)
		indexLength        = make(map[string][]int)
		newIndexColumn     = make(map[string][]string)
		newIndexColumnMeta = make(map[string][]global.TableMeta)
		indexNameType      = make(map[string] /*indexName + indexType*/ string)
		columnGather       = make(map[string] /*indexName + newIndexColumn */ []string)
	)
	if len(s.TableMeta.IndexMeta) == 0 {
		return
	}
	im, ok := s.TableMeta.IndexMeta["source"]
	if !ok {
		return
	}
	if len(im.IndexColumn) == 0 {
		return
	}
	//索引名及对应的索引类型集合
	for k := range im.IndexColumn {
		if t, ok1 := im.IndexType[k]; ok1 {
			indexNameType[k] = t
		}
	}
	for k, v := range im.IndexColumn {
		if t, ok1 := im.IndexType[k]; ok1 {
			var indexColumn1 []string
			for _, w := range v {
				indexColumn1 = append(indexColumn1, w.ColumnName)
			}
			switch t {
			case "uni", "pri":
				oldIndexColumn := indexColumn1
				add, _ := ea.CheckSum().Arrcmp(partitionColumn, indexColumn1)
				indexColumn1 = append(indexColumn1, add...)
				if len(add) > 0 {
					recover = append(recover, global.HeterogeneousConvertRecord{
						Object:   "index uniq",
						Logo:     k,
						OldValue: strings.Join(oldIndexColumn, ","),
						NewValue: strings.Join(indexColumn1, ","),
						Reason:   "primary or unique key must Include partition column",
					})
				}
			}
			newIndexColumn[k] = indexColumn1
		}
	}
	for k, v := range newIndexColumn {
		var newIndexLength int
		for _, w := range v {
			for _, l := range s.TableMeta.ColumnMeta["source"] {
				if strings.EqualFold(l.ColumnName, w) {
					_, p := or.TypeConvert(l, factor)
					newIndexLength += p
					if y, z := newIndexColumnMeta[k]; z {
						newIndexColumnMeta[k] = append(y, l)
					} else {
						newIndexColumnMeta[k] = []global.TableMeta{l}
					}
					if y, z := indexLength[k]; z {
						indexLength[k] = append(y, p)
					} else {
						indexLength[k] = []int{p}
					}
				}
			}
		}
		indexMaxLength[k] = newIndexLength
	}

	for k, v := range indexMaxLength {
		avgLength := 3072 / len(indexLength[k])
		if v > 3072 {
			var newColumnGarth []string
			for n, m := range indexLength[k] {
				if m > avgLength {
					newColumnGarth = append(newColumnGarth, fmt.Sprintf("`%v`(%v)", newIndexColumn[k][n], avgLength/factor))
				} else {
					newColumnGarth = append(newColumnGarth, fmt.Sprintf("`%v`", newIndexColumn[k][n]))
				}
			}
			recover = append(recover, global.HeterogeneousConvertRecord{
				Object:   "index size",
				Logo:     k,
				OldValue: strings.Join(newIndexColumn[k], ","),
				NewValue: strings.Join(newColumnGarth, ","),
				Reason:   "index length >3072",
			})
			columnGather[k] = newColumnGarth
		} else {
			var newColumnGarth []string
			for n := range indexLength[k] {
				newColumnGarth = append(newColumnGarth, fmt.Sprintf("`%v`", newIndexColumn[k][n]))
			}
			columnGather[k] = newColumnGarth
		}
	}
	for k, v := range columnGather {
		switch indexNameType[k] {
		case "mul":
			creatText = append(creatText, fmt.Sprintf("KEY `%v` (%v)", k, strings.Join(v, ",")))
		case "uni":
			creatText = append(creatText, fmt.Sprintf("UNIQUE KEY `%v` (%v)", k, strings.Join(v, ",")))
		case "pri":
			notNull = newIndexColumn[k]
			creatText = append(creatText, fmt.Sprintf("PRIMARY KEY `%v` (%v)", k, strings.Join(v, ",")))
		}
	}
	indexSql = fmt.Sprintf("%v", strings.Join(creatText, ",\n"))
	return
}
func commentAction(prefix, s string) string {
	var replicationStr = []string{"'", ";"}
	switch {
	case s == "<entry>", s == "<null>", s == "''", s == "NULL":
		return fmt.Sprintf("%v ''", prefix)
	case len(s) == 0:
		return ""
	case func() bool {
		var e bool
		for _, v := range replicationStr {
			if strings.Contains(s, v) {
				e = true
			}
		}
		return e
	}():
		var q = s
		for _, v := range replicationStr {
			if strings.Contains(s, v) {
				q = strings.ReplaceAll(q, fmt.Sprintf("%v", v), fmt.Sprintf("\\%v", v))
			}
		}
		return fmt.Sprintf("%v '%v'", prefix, q)
	default:
		return fmt.Sprintf("%v '%v'", prefix, s)
	}
}
func (or TableColumn) createTableBody(s global.GetColumnTypeInput, notNull []string, tableIndexBody string, factor int) (recover []global.HeterogeneousConvertRecord, p string, err error) {
	var (
		creatText       []string
		createTableHead = fmt.Sprintf("CREATE TABLE IF NOT EXISTS `%v`.`%v` (\n", s.TableInfo.Schema, s.TableInfo.Table)
		maxSize         int
		cByte           int
		byteMerge       = make(map[int][]string)
		byteOrder       []int
	)
	if len(s.TableMeta.ColumnMeta) == 0 {
		return
	}
	//计算行长度
	for _, a := range s.TableMeta.ColumnMeta["source"] {
		_, cByte = or.TypeConvert(a, factor)
		maxSize += cByte
		if b, ok := byteMerge[cByte]; ok {
			byteMerge[cByte] = append(b, a.ColumnName)
		} else {
			byteMerge[cByte] = []string{a.ColumnName}
			byteOrder = append(byteOrder, cByte)
		}
	}
	//倒序排列
	sort.Slice(byteOrder, func(i, j int) bool {
		return byteOrder[i] > byteOrder[j]
	})
	var varcharModifyText []string
	if maxSize > 65527 {
		tmpMaxSize := maxSize
	ll:
		for _, v := range byteOrder {
			for i := 0; i < len(byteMerge[v]); i++ {
				if tmpMaxSize < 65527 {
					break ll
				} else {
					varcharModifyText = append(varcharModifyText, byteMerge[v][i])
					tmpMaxSize = tmpMaxSize - v
				}
			}
		}
	}
	for _, a := range s.TableMeta.ColumnMeta["source"] {
		var (
			TypeConstraint string
			nullConstraint = fmt.Sprintf(" NULL ")
			ChartSetValue  string
			CollationValue string
			DefaultValue   string
		)
		TypeConstraint, _ = or.TypeConvert(a, factor)
		for _, b := range varcharModifyText {
			if strings.EqualFold(b, a.ColumnName) {
				switch a.ConvertColumnType.Type {
				case "varchar":
					TypeConstraint = "text"
					recover = append(recover, global.HeterogeneousConvertRecord{
						Object:   "table size",
						Logo:     b,
						OldValue: a.ColumnType,
						NewValue: TypeConstraint,
						Reason:   "table length >65535",
					})
				}
			}
		}
		if strings.EqualFold(a.IsNull, "NO") {
			nullConstraint = fmt.Sprintf(" NOT NULL ")
		} else {
			for _, z := range notNull {
				if strings.EqualFold(a.ColumnName, z) {
					nullConstraint = fmt.Sprintf(" NOT NULL ")
					recover = append(recover, global.HeterogeneousConvertRecord{
						Object:   "column null",
						Logo:     a.ColumnName,
						OldValue: "NULL",
						NewValue: "NOT NULL",
						Reason:   "primary key must is not null",
					})
				}
			}
		}
		if len(a.ColumnDefault) > 0 || strings.EqualFold(a.ColumnDefault, "''") {
			if !strings.EqualFold(a.ConvertColumnType.Type, "text") {
				DefaultValue = fmt.Sprintf("DEFAULT %v", or.DefaultValueConvert(a.DataType, a.ConvertDefaultValue))
			} else {
				recover = append(recover, global.HeterogeneousConvertRecord{
					Object:   "column default",
					Logo:     a.ColumnName,
					OldValue: a.ConvertDefaultValue.OldValue,
					NewValue: "",
					Reason:   "text date Type not use default value",
				})
			}
		}
		creatText = append(creatText, fmt.Sprintf("`%v` %v %v %v %v %v %v ", a.ColumnName, TypeConstraint, nullConstraint, DefaultValue, ChartSetValue, CollationValue, commentAction("COMMENT ", a.ColumnComment)))
	}
	p = fmt.Sprintf("%v %v \n) %v %v %v ", createTableHead, strings.Join(creatText, fmt.Sprintf(",\t\n")), commentAction("DEFAULT CHARSET= ", s.TableMeta.Comment["source"].Character), commentAction("COLLATE= ", s.TableMeta.Comment["source"].Collate), commentAction("COMMENT ", s.TableMeta.Comment["source"].Comment))
	if len(tableIndexBody) > 0 {
		p = fmt.Sprintf("%v %v,\n %v \n) %v %v %v", createTableHead, strings.Join(creatText, fmt.Sprintf(",\t\n")), tableIndexBody, commentAction("DEFAULT CHARSET= ", s.TableMeta.Comment["source"].Character), commentAction("COLLATE= ", s.TableMeta.Comment["source"].Collate), commentAction("COMMENT ", s.TableMeta.Comment["source"].Comment))
	}
	return
}
func (or TableColumn) CreateSql(s global.GetColumnTypeInput) (result global.CreatSqlResult, err error) {
	var (
		tableBody string
		factor    int
		//分区相关参数
		tablePartitionBody string
		partitionColumn    []string
		//索引相关的参数
		notNull         []string
		tableIndexBody  string
		record, record1 []global.HeterogeneousConvertRecord
	)
	record1, partitionColumn, tablePartitionBody = or.createTableSqlPartitionBody(s)
	record = append(record, record1...)
	record1, notNull, tableIndexBody = or.CreateIndex(s, partitionColumn, factor)
	record = append(record, record1...)
	record1, tableBody, err = or.createTableBody(s, notNull, tableIndexBody, factor)
	record = append(record, record1...)
	/*
		遇到的问题
		1: 索引长度超限（767），必要时可使用前缀索引或减少数据类型长度   已解决
		2：行长度超限（65535），超过优先转varchar最大的，逐层依次降低  已解决
		3：partition column 不能有comment，create table不受限制 alter comment 受限制   已解决
		4：partition column type 是decimal，需要转换为flot    已解决
		5：partition column type 是varchar,datetime，需要增加partition by range columns  已解决
		6：表存在主键索引或唯一索引时，需要包含分区列     已解决
		7：range 或list 分区 values less than ('') 存在单引号且数据类型为int类型，转换失败  已解决
		8：mysql体系不允许大字段有blobText，json，longtext 有默认值问  已解决
		9:  comment 中包含单引号或者;号问题     已解决
	*/
	return global.CreatSqlResult{
		Record: record,
		Sql:    fmt.Sprintf("%v %v;", tableBody, tablePartitionBody),
	}, nil
}
